﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;

namespace produccion.clases
{
    public class CLSENCPPRP
    {
        MDOgeneral mod = new MDOgeneral();
        string codprv_epp, estped_epp, observ_epp, nroexp_epp,descripcion;
        DateTime fecped_epp, fecent_epp;
        long nroped_epp;

        public string CODPRV_EPP
        {
            get { return codprv_epp; }
            set { codprv_epp = value; }
        }

        public long NROPED_EPP
        {
            get { return nroped_epp; }
            set { nroped_epp = value; }
        }

        public DateTime FECPED_EPP
        {
            get { return fecped_epp; }
            set { fecped_epp = value; }
        }

        public DateTime FECENT_EPP
        {
            get { return fecent_epp; }
            set { fecent_epp = value; }
        }

        public string ESTPED_EPP
        {
            get { return estped_epp; }
            set { estped_epp = value; }
        }

        public string OBSERV_EPP
        {
            get { return observ_epp; }
            set { observ_epp = value; }
        }

        public string NROEXP_EPP
        {
            get { return nroexp_epp; }
            set { nroexp_epp = value; }
        }

        public string DESCRIPCION
        {
            get { return descripcion; }
            set { descripcion = value; }
        }

        public void LimpiarValores()
        {
            CODPRV_EPP = "";
            ESTPED_EPP = "";
            OBSERV_EPP = "";
            NROEXP_EPP = "";
        }

        public Boolean ExisteMP(string Codigo, string Color)
        {
            try
            {
                string query = "select codmat_mtp, codcol_mtp from maemtp where codmat_mtp='" + Codigo + "' and codcol_mtp='" + Color + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public long Consecutivo(string CodPrv)
        {
            string strValorRetornado;//FJRF - Capturamos el valor retornado
            try
            {
                string query = "select max(nroped_epp) + 1 from encpprp where codprv_epp='" + CodPrv + "'";

                if (mod.consulta(query).Rows.Count > 0)
                {
                    strValorRetornado = mod.consulta(query).Rows[0][0].ToString();

                    if (String.IsNullOrEmpty(strValorRetornado) == true)
                    {
                        return 1;
                    }
                    else
                    {
                        return System.Convert.ToInt64(strValorRetornado);
                    }
                }
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public double ExistenciaMP(string Codigo, string Color)
        {
            try
            {
                string query = "select (eximtp_mtp-cansep_mtp) as eximtp_mtp from maemtp where codmat_mtp='" + Codigo + "' and codcol_mtp='" + Color + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return System.Convert.ToDouble( mod.consulta(query).Rows[0][0].ToString());
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public double CostoMP(string Codigo, string Color)
        {
            MDOgeneral MdoGeneral = new MDOgeneral();//FJRF - Instanciamos las formulas

            String StrValorRetornado;//FJRF - Valor que retorna la consulta
            Double DblValorARetornar = 0;//FJRF - Valor a retornar [Se inicia en cero]
            
            try
            {
                StrValorRetornado = MdoGeneral.StrRetornarUnSoloCampo("valor_pmp", "Costo", "premtpp", "codmat_pmp='" + Codigo + "' and codcol_pmp='" + Color + "' and codlis_pmp='99'");//FJRF - Hacemos la consulta comn codigo de lista 99

                //FJRF - CODIGO LISTA 99
                if (StrValorRetornado.Trim() == "DATO NO ENCONTRADO" || StrValorRetornado.Trim() == "0")//FJRF - Si no encontramos un valor hacemos lo siguiente
                {
                    StrValorRetornado = MdoGeneral.StrRetornarUnSoloCampo("valor_pmp", "Costo", "premtpp", "codmat_pmp='" + Codigo + "' and codcol_pmp='" + Color + "' and codlis_pmp='00'");//FJRF - Hacemos la consulta comn codigo de lista 00

                    //FJRF - CODIGO LISTA 00
                    if (StrValorRetornado.Trim() == "DATO NO ENCONTRADO" || StrValorRetornado.Trim() == "0")//FJRF - Si no encontramos un valor hacemos lo siguiente
                    {
                        DblValorARetornar = 0;//FJRF - Retornamos el valor
                    }
                    else
                    {
                        DblValorARetornar = System.Convert.ToDouble(StrValorRetornado.Trim());//FJRF - Retornamos el valor
                    }
                }
                else
                {
                    DblValorARetornar = System.Convert.ToDouble(StrValorRetornado.Trim());//FJRF - Retornamos el valor
                }

                return DblValorARetornar;//FJRF - Retornamos el valor encontrado
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string NombreMP(string Codigo, string Color)
        {
            try
            {
                string query = "select trim(nommat_mat) || ' - ' || trim(nomcol_col) as descripcion from maemat inner join maemtp on codmat_mat=codmat_mtp inner join maecolp on codcol_mtp=codcol_col where codmat_mtp='" + Codigo + "' and codcol_mtp='" + Color + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    DESCRIPCION = mod.consulta(query).Rows[0][0].ToString().Trim();
                }
                return DESCRIPCION;                
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public bool ExistePedido()
        {
            try
            {
                string query = "select codprv_epp, nroped_epp from encpprp where codprv_epp='" + CODPRV_EPP + "' and nroped_epp=" + NROPED_EPP;
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ConsultarTodas()
        {
            try
            {
                string query = "select codprv_epp, nroped_epp, fecped_epp, fecent_epp, estped_epp, observ_epp, nroexp_epp from encpprp order by codprv_epp, nroped_epp";
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count.Equals(0))
                {
                    query = "select '' as codprv_epp, 0 as nroped_epp, '01/01/1900' as fecped_epp, '01/01/1900' as fecent_epp, '' as estped_epp, '' as observ_epp, '' as nroexp_epp from encpprp";
                    dt = mod.consulta(query);
                    return dt;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Consultar()
        {
            try
            {
                string query = "select fecped_epp, fecent_epp, estped_epp, observ_epp, nroexp_epp from encpprp where codprv_epp='" + CODPRV_EPP + "' and nroped_epp=" + NROPED_EPP;
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    FECPED_EPP = System.Convert.ToDateTime(dt.Rows[0][0].ToString());
                    FECENT_EPP = System.Convert.ToDateTime(dt.Rows[0][1].ToString());
                    ESTPED_EPP = dt.Rows[0][2].ToString();
                    OBSERV_EPP = dt.Rows[0][3].ToString();
                    NROEXP_EPP = dt.Rows[0][4].ToString();
                }
                else
                {
                    FECPED_EPP = MDOgeneral.DttFechaActualDeValidacionEnElSistema;//FJRF - Ponemos la fecha del dia actual del mes seleccionado por el sistema;
                    FECENT_EPP = MDOgeneral.DttFechaActualDeValidacionEnElSistema;//FJRF - Ponemos la fecha del dia actual del mes seleccionado por el sistema;
                    ESTPED_EPP = "";
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Imprimir()
        {
            try
            {
                string query = "select CODPRV_EPP, NOMPRV_PRV, NROPED_EPP, FECPED_EPP, FECENT_EPP, ";
                query = query + "case when ESTPED_EPP = 'P' then 'Pendiente' " + " ";
                query = query + "ELSE case when ESTPED_EPP = 'C' then 'Completo' " + " ";
                query = query + "ELSE case when ESTPED_EPP = 'X' then 'Anulado'" + " ";
                query = query + "ELSE case when ESTPED_EPP = 'p' then 'Pendiente por confirmar' " + " ";
                query = query + "END END END END AS ESTPED_EPP, " + " ";
                query = query + "OBSERV_EPP, NROEXP_EPP, CODPRV_EPP || '' || LPAD(TRIM(TO_CHAR(NROPED_EPP)),4,'0') AS ORDENCOMPRA " + " ";
                query = query + "from encpprp inner join maeprvp on encpprp.codprv_epp=codprv_prv where " + " ";
                query = query + "codprv_epp = '" + CODPRV_EPP + "' and nroped_epp = " + NROPED_EPP;
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable ImprimirConsultaOrdenCompra(bool Pendiente)
        {
            try
            {
                string query;
                if (Pendiente == true)
                {
                    query = "select CODPRV_EPP, NOMPRV_PRV, NROPED_EPP, FECPED_EPP, FECENT_EPP, ESTPED_EPP, OBSERV_EPP, NROEXP_EPP from encpprp inner join maeprvp on encpprp.codprv_epp=codprv_prv where codprv_epp='" + CODPRV_EPP + "' and ESTPED_EPP='p'";
                }
                else
                {
                    query = "select CODPRV_EPP, NOMPRV_PRV, NROPED_EPP, FECPED_EPP, FECENT_EPP, ESTPED_EPP, OBSERV_EPP, NROEXP_EPP from encpprp inner join maeprvp on encpprp.codprv_epp=codprv_prv where codprv_epp='" + CODPRV_EPP + "'";
                }
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable ImprimirConsultaOrdenCompraMaterial(string Material)
        {
            try
            {
                string query;
                query = "select CODPRV_EPP, NOMPRV_PRV, NROPED_EPP, FECPED_EPP, FECENT_EPP, ESTPED_EPP, OBSERV_EPP, NROEXP_EPP from encpprp inner join detpprp on codprv_epp=codprv_dpp and nroped_epp=nroped_dpp inner join maeprvp on encpprp.codprv_epp=codprv_prv where codmat_dpp='" + Material + "'";                
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable PedidosPorAutorizar()
        {
            try
            {
                string query = "select nroped_epp as Pedido,codprv_epp as Codigo,nomprv_prv as Proveedor,fecped_epp as Fecha,nroexp_epp as Explosion,sum(canpro_dpp) as Cantidad from Encpprp inner join maeprvp on codprv_epp=codprv_prv inner join detpprp on nroped_epp=nroped_dpp and codprv_epp=codprv_dpp where estped_epp='p' group by nroped_epp, codprv_epp, nomprv_prv, fecped_epp, nroexp_epp order by codprv_epp, nroped_epp";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
       
        public int Eliminar()
        {
            try
            {
                MDOgeneral MdoGeneral = new MDOgeneral();
                String StrResultado = MdoGeneral.StrRetornarUnSoloCampo("estped_epp", "Estado", "encppr", "codprv_epp='" + CODPRV_EPP + "' and nroped_epp=" + NROPED_EPP +" ");
                if (StrResultado == "p")
                {
                    MdoGeneral.comando("DELETE FROM ENCPPRP WHERE codprv_epp='" + CODPRV_EPP + "' and nroped_epp=" + NROPED_EPP + " ");
                    MdoGeneral.comando("DELETE FROM DETPPRP WHERE codprv_dpp='" + CODPRV_EPP + "' and nroped_dpp=" + NROPED_EPP + " ");
                    return 1;
                }
                else
                {
                    string query = "update encpprp set estped_epp='X' where codprv_epp='" + CODPRV_EPP + "' and nroped_epp=" + NROPED_EPP;
                    return mod.comando(query);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Insertar()
        {
            try
            {
                if (NROEXP_EPP == null)
                {
                    NROEXP_EPP = "0";
                }
                string query = "insert into encpprp(codprv_epp, nroped_epp, fecped_epp, fecent_epp, estped_epp, observ_epp, nroexp_epp) values ('" + CODPRV_EPP + "'," + NROPED_EPP + ",'" + FECPED_EPP.ToShortDateString() + "','" + FECENT_EPP.ToShortDateString() + "','" + ESTPED_EPP + "','" + OBSERV_EPP + "','" + NROEXP_EPP + "')";
                return mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Actualizar()
        {
            try
            {
                if (NROEXP_EPP == null)
                {
                    NROEXP_EPP = "0";
                }
                string query = "update encpprp set fecped_epp='" + FECPED_EPP.ToShortDateString() + "',fecent_epp='" + FECENT_EPP.ToShortDateString() + "', observ_epp='" + OBSERV_EPP + "', nroexp_epp='" + NROEXP_EPP + "',estped_epp='" + ESTPED_EPP + "' where codprv_epp='" + CODPRV_EPP + "' and nroped_epp=" + NROPED_EPP;
                return mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Buscar()
        {
            try
            {
                string query = "select nroped_epp as pedido, fecped_epp as fecha, estped_epp as estado, sum(canpro_dpp) as cant, sum(canrec_dpp) as rec, sum(candes_dpp) as desca, sum(canpro_dpp) - sum(canrec_dpp) - sum(candes_dpp) as pend from encpprp inner join detpprp on codprv_epp=codprv_dpp and nroped_epp=nroped_dpp where codprv_epp='" + CODPRV_EPP + "' group by nroped_epp, fecped_epp, estped_epp order by nroped_epp desc";
                return mod.consulta(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ConsultaOrdenCompra()
        {
            try
            {
                string query = "select nroped_epp as Nro, fecped_epp as Fecha, case when estped_epp='p' then 'Pendiente' else case when estped_epp='P' then 'Por confirmar' else 'Anulado' end end as Estado, fecent_epp as A_Entregar, sum(canpro_dpp) as Pedido, sum(canrec_dpp) as Recib, sum(candes_dpp) as Desca, 0 as De_Mas, sum(canpro_dpp - canrec_dpp - candes_dpp) as Pend from encpprp inner join detpprp on codprv_epp=codprv_dpp and nroped_epp=nroped_dpp where codprv_epp='" + CODPRV_EPP + "' group by nroped_epp, fecped_epp, estped_epp, fecent_epp order by nroPed_epp desc";
                return mod.consulta(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
